<?php
namespace app\examScore\controller;
use app\common\Base;
use app\common\Excel;
use think\Db;
//--单科奖
class ExamDankePrize extends Base {
	
	public function index() {
		$fields = "班级,学号,姓名,学科,学科名次";
		$list = explode(",", $fields);
		$this->assign('list',$list);
		return $this->fetch();
	}

	public function pageQuery() {
		$examId = input("examId");
		$grade = input("grade");
		$clazz = input("clazz");
		if(!$examId) return [];
		if($grade) {
			$array = explode("_", $grade);
			$grade = $array[0];
		}
		$gradeId = intval(input("gradeId"));
		$page = input("page");
		$rows = input("rows");
		$where="";
		if($clazz) {
			$clazz = $gradeId*100 + intval($clazz);
			$where["班级"] = $clazz;
		}
		$sql = "";
		$subjectList = $this::examSubject($examId);
		foreach ($subjectList as $key => $obj) {
			$subject = $obj["subject"];
			$sql = $sql."select 班级,学号,姓名,'".$subject."' as 学科,".$subject."名次 as 学科名次 from 成绩".$grade." where 考试ID=".$examId." and ".$subject."名次 <= 10";
			if(intval($key) + 1 < count($subjectList)) {
				$sql = $sql." union ";
			} 
		}
		$result = Db::table("(".$sql.") a")
				  ->field("班级,学号,姓名,学科,学科名次")
				  ->where($where)
				  ->order("学科,学科名次")
				  ->page($page, $rows)
				  ->select();
		$count = Db::table("(".$sql.") a")
				  ->field("班级,学号,姓名,学科,学科名次")
				  ->where($where)
				  ->count();
		$grid["rows"] = selectResult($result);
		$grid["total"] = $count;
		return $grid;
	}

	public function exportExcel() {
		$examId = input("examId");
		$grade = input("grade");
		$clazz = input("clazz");
		if(!$examId) return [];
		if($grade) {
			$array = explode("_", $grade);
			$grade = $array[0];
		}
		$gradeId = intval(input("gradeId"));
		$where="";
		if($clazz) {
			$clazz = $gradeId*100 + intval($clazz);
			$where["班级"] = $clazz;
		}
		$sql = "";
		$subjectList = $this::examSubject($examId);
		foreach ($subjectList as $key => $obj) {
			$subject = $obj["subject"];
			$sql = $sql."select 班级,学号,姓名,'".$subject."' as 学科,".$subject."名次 as 学科名次 from 成绩".$grade." where 考试ID=".$examId." and ".$subject."名次 <= 10";
			if(intval($key) + 1 < count($subjectList)) {
				$sql = $sql." union ";
			} 
		}
		$result = Db::table("(".$sql.") a")
				  ->field("班级,学号,姓名,学科,学科名次")
				  ->where($where)
				  ->order("学科,学科名次")
				  ->select();
		$result = selectResult($result);
		//excel表头内容
		$fields = "班级,学号,姓名,学科,学科名次";
		$list = explode(",", $fields);
		$header = [];
		foreach ($list as $key => $value){
			$header[$value] = $value;
		}
		Excel::toExcel($result,'单科奖',$header);
		exit;
	}

	public function examSubject($examId){
		$where["考试ID"] = $examId;
		$result = Db::table("成绩汇总")
			   ->field("学科 subject")
			   ->distinct(true)
			   ->where($where)
			   ->select();
		return $result;
	}

}
